Power BI Automated Reports using Power Automate

By: Kenneth A. Omorodion   |   Updated: 2021-09-03   |   Comments (8)   |   Related: > Power BI

Power BI reports are usually meant to be viewed within the Power BI Service (Powerbi.com). Recently, it has become easier to view Power BI reports within SharePoint Online and Microsoft Teams. However, there are cases where end users might not be able to view these reports within these platforms maybe due to permissions or licence issues within an organisation and so would require alternative ways to view the reports, or maybe the end users just need these reports to be emailed in PDF or Word formats on a set schedule for some reason. Although there is already a way to send full reports in PDF or PPTX formats on a set schedule using Power BI subscriptions, there currently are some limitations in that you might not be able to subscribe external users that are not in your organisations Active Directory due to organisational policies (this can be overcome by enabling email subscriptions and sharing content with external users in the Power BI Admin area), and there is currently no way to filter specific page(s) to export other than exporting full report pages using the subscriptions option.

The good news is that there is a workaround for this, which can be achieved using Power Automate. There have been loads of articles and videos done on various tasks we can use to automate with Power Automate and this is another unique one in that it helps to solve some of the limitations of the Power BI service as mentioned above.

Before we take a deep dive on how to schedule an export and emailing of Power BI reports pages using Power Automate let us first look at some things to remember and implement in the Power BI report you are to export and email.

So, with the five points mentioned above in mind, let us now demonstrate how to achieve this task.

For this demo, I have used the sample dataset as seen below just for the purpose of this article. Your dataset might be different, but the principles and logic remain the same as would be demonstrated in this article.

I have created a Power BI report with three pages for each Sales Territory Region. For this demo, we are assuming we need to automate the export and sending of the three pages to different offices (i.e., United Kingdom office, France office, and Germany office) via email in PDF format. This would be based on a schedule, for instance, the reports are expected every Monday morning. This might be the end of each month in your case or end of each day, just ensure the data meets the three points mentioned earlier.

Let’s now break down the whole process into easily understandable steps to achieve this task.

Once the report development is completed, you would need to publish the report to a Premium capacity workspace in Power BI service (or at least one workspace in the Power BI tenant should have a reserved capacity). Also, it is important to note that you might not be able to complete all steps mentioned above for the flow to be set up unless you have at least a "Member" access permission or above in your chosen workspace, having a "Contributor" access might not be enough.

You can login to Power Automate using flow.microsoft.com. After successful login, you would need to do the following.

Click on the "Create" tab on the left blade as seen in the diagram below.

Next, select "Scheduled cloud flow" to create the flow from scratch.

Then on the new window that opens, you need to give your flow a name, I have named this demo flow "PDF Export and Email".

Next, on the "Run this Flow" section chose a Starting date and the time it should be running the flow as well as the frequency of running.

On the "On these days" section just choose the days of the week you need it to run.

Then click "Create" as seen in the diagram below.

Once created, the Scheduled cloud flow first step should look like the diagram below.

Next, we need to configure the recurrence flow step by following the following steps.

First, click on the recurrence step, and then click on " Edit " as seen in the diagram below.

Then, click on the dropdown on the "Show advanced options" as seen in the diagram below.

Next, enter a value in "At these hours" and "At these minutes" to set a specific time for your flow to run. I have entered 7:30am in mine as seen in the diagram below.

To start creating the next step we need to click on "Next Step" at the bottom of the "Recurrence" step we configured in step 3. The step is opened as seen in the diagram below.

Next, we need to do the following to create this step of the flow.

On the search bar for "Search connectors and actions" we search for Power BI and select Export to File for Power BI Reports as seen in the diagram below.

Next, enter the name of the premium capacity workspace in Power BI service that you have published the reports to in step 1 above in the "Workspace" section or click on the dropdown to choose among the lists. Do the same for the "Report" section. For the "Export format" we are choosing "PDF" for this demo. You can also try out the other formats. On the "Include Hidden Pages" section you can choose to either include hidden pages in your export or hide them, I have entered "No" to exclude hidden pages in my export in this demo. See the diagram below.

After this, we need to complete the sections where we can indicate specific pages we need to export. If we do not include specific pages, by default all pages of the report would be exported and emailed. But we can specify specific pages to be exported as follows.

First, we need to go to Power BI service, and open the report we need to export. On the page you need exported you need to copy the last part of the URL as seen in the diagram below.

Then, paste this copied URL part on the "Pages pageName - 1" section as seen in the diagram below. This should only export the "United Kingdom" report page alone.

To add other pages of the report like the "Germany" report page to this export already containing that of United Kingdom, we need to copy the last part of the URL in the "Germany" report in Power BI service as we did for United Kingdom above, then click on "Add new item" and paste the copied URL part into the "Pages pageName – 2" section as seen in the diagram below.

That would be all for this step of the flow for now. I will write another article that demonstrates how to export and email reports which have Role Level Security.

After the step 4 above, click "Next step" as seen in the diagram below.

Next, on the search bar for "Search connections and actions" enter "Outlook" and search for "Send an email v2" as seen in the diagram below.

Once the flow window opens, complete the To, Subject, and Body fields for your email as seen in the diagram below. You can enter as many emails as required separated by semicolons. In the demo example I have only used one email address. You can also give any name for the subject as well as enter any custom message for the body of the email as you require.

Next, you need to select "Show advanced options" as seen in the diagram above. In "Attachments Name – 1", enter a name for your attachment and add a file extension to the file name (for example, .PDF) that matches your desired Export Format. See diagram below for example of this.

Next, click within "Attachment Content", select "File Content" from among the dynamic content to attach your exported Power BI report. Follow the steps in the diagram below to do this. Once you click on the "File Content" it then gets attached into the "Attachment Content" section, and that should be all done.

Next, you save the flow by clicking on "Save" at the bottom of the flow step.

At this step you need to test the flow after saving. Power Automate will create and evaluate the flow, and let you know if there are any errors.

If there are errors, select "Edit flow" to fix them. Otherwise, select the Back arrow to view the flow details and run the flow. When you run the flow, Power Automate exports your Power BI report in the format you specified (PDF in this case) and sends it as an email attachment as scheduled.

If your flow is error free, after saving you should see a message on a green background as seen in the diagram below. The message should also advice you to test your flow.

To test your flow, follow the steps below as also shown in the diagrams below.

Click on "Test"

Next, select "Manually" and click on Test at the bottom.

Next, click on "Run Flow" and "Done". Once you click on "Done" the flow starts running and when completed you should see something like the diagram below.

When you check your email box (or the email box of the users you have sent this to) you should be able to see the PDF attachment with the pages of the report you have configured. As explained earlier, if you need all the pages of the report to be sent in one PDF file then don’t include any page URL extensions at all and it should send all pages by default. See the diagram below for the attachment sent to my inbox.

In summary, we have successfully demonstrated how to export and email a PDF format of pages of a Power BI report to users both within and outside of an organisation (does not require Active Directory accounts). It is important to note that this solution can be applied in many other business cases like an alternative solution for Row Level Security by sending pages of a report to specific teams, offices, regions, or departments depending on business requirements. Sometimes you may just need to add additional flow steps to achieve this or in other cases if you have too many categories (e.g., more than ten Offices, Regions, or Teams) you might need to be a little creative by creating separate flows to meet your needs.